package cn.edu.lingnan.dao;



import cn.edu.lingnan.pojo.Admin;
import cn.edu.lingnan.pojo.User;
import cn.edu.lingnan.utills.MD5;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class userdaoimp implements userdao{
    //登录验证
    @Override
    public Admin Login(String username, String password) {
        Admin admin=null;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.admin where username =? and password =? ");

            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){

                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                String name = rs.getString("name");
                admin=new Admin(username1,password1,name);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return  admin;
    }


 //查询所有学生
    @Override
    public List<User> selectall() {
        List<User> userlist = new ArrayList<User>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.student ");


            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                int age = rs.getInt("age");
                String gender = rs.getString("gender");
                String major = rs.getString("major");
                String name = rs.getString("name");
                Integer identity = rs.getInt("identity");
                String ClassName = rs.getString("class");
                String address = rs.getString("address");
                String phone = rs.getString("phone");

                User user = new User(id, name,username, password,age,gender,major, ClassName, address, phone, identity);
                userlist.add(user);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return userlist;
    }

    //注册
    @Override
    public Integer registered(User user) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.student (username, password, age, gender, major, name, identity) values(?,?,?,?,?,?,?) ");

              ps.setString(1,user.getUsername());
              ps.setString(2, MD5.md5Encrypt(user.getPassword())); // 先对密码进行MD5加密
              ps.setInt(3,user.getAge());
              ps.setString(4,user.getGender());
              ps.setString(5,user.getMajor());
              ps.setString(6,user.getName());
              ps.setInt(7,1);

            Integer count = ps.executeUpdate();
            return count;

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //单个和批量删除
    @Override
    public Integer deleteUser(Integer[] idsInt) {
        int rowsAffected = 0;

        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                    "root", "123456"
            );
            // 关闭自动提交，开启事务
            conn.setAutoCommit(false);

            // 删除score表中的记录
            PreparedStatement psScore = conn.prepareStatement("DELETE FROM school.score WHERE sid = ?");
            for (Integer id : idsInt) {
                psScore.setInt(1, id);
                psScore.addBatch(); // 将删除操作添加到批处理
            }
            psScore.executeBatch(); // 执行批处理

            // 删除student表中的记录
            PreparedStatement psStudent = conn.prepareStatement("DELETE FROM school.student WHERE id = ?");
            for (Integer id : idsInt) {
                psStudent.setInt(1, id);
                psStudent.addBatch(); // 将删除操作添加到批处理
            }
            int[] results = psStudent.executeBatch(); // 执行批处理
            conn.commit(); // 提交事务

            // 计算受影响的行数
            for (int result : results) {
                rowsAffected += result;
            }

            psScore.close();
            psStudent.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        return rowsAffected;
    }

  //修改学生信息
    @Override
    public Integer updateUser(User user ) {
        Integer rowsAffected = 0;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.student set   username=?,password=?,age=?,gender=?,major=?,name=?where id = ?");
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setInt(3,user.getAge());
            ps.setString(4,user.getGender());
            ps.setString(5,user.getMajor());
            ps.setString(6,user.getName());
            ps.setInt(7,user.getId());
            return ps.executeUpdate();

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }



    }
    //根据id获取
    @Override
    public User getById(Integer id) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.student where id = ?");

             ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            User user = new User();
            if (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                user.setGender(rs.getString("gender"));
                user.setMajor(rs.getString("major"));
            }
            return user;
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Integer addUser(User user) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.student (id, username, password, age, gender, major, name, identity) values(?,?,?,?,?,?,?,?) ");
            ps.setInt(1,user.getId());
          ps.setString(2,user.getUsername());
          ps.setString(3,user.getPassword());
          ps.setInt(4,user.getAge());
          ps.setString(5,user.getGender());
          ps.setString(6,user.getMajor());
          ps.setString(7,user.getName());
         ps.setInt(8,0);

            Integer count = ps.executeUpdate();
            return count;

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> getUserLikeName(String name) {
        List<User> userlist = new ArrayList<User>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.student where name like ?");

            ps.setString(1, "%" + name + "%");
            ResultSet rs = ps.executeQuery();
            User user = new User();
           while(rs.next()) {
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                user.setGender(rs.getString("gender"));
                user.setMajor(rs.getString("major"));
                userlist.add(user);
            }
            return userlist;
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public User UserLogin(String username, String password) {
      User user=null;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.student where username =? and password =? ");

            ps.setString(1, username);
            ps.setString(2, MD5.md5Encrypt(password));
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                int id = rs.getInt("id");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String gender = rs.getString("gender");
                String major = rs.getString("major");
                String ClassName = rs.getString("class");
                String address = rs.getString("address");
                String phone = rs.getString("phone");
                Integer identity = rs.getInt("identity");
                user=new User( id,name,username1,password1,age,gender,major, ClassName, address, phone,identity);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return  user;
    }

    @Override
    public Integer EnabledAndDisabled(Integer id, Integer identity) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.student set  identity=?  where id = ?");



            ps.setInt(1,identity);

            ps.setInt(2,id);
            return ps.executeUpdate();

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Integer StudentUpdate(User user) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.student set   age=?,gender=?,major=?,name=?,class=?,address=?,phone=? where id = ?");
            ps.setInt(1,user.getAge());
            ps.setString(2,user.getGender());
            ps.setString(3,user.getMajor());
            ps.setString(4,user.getName());
            ps.setString(5,user.getClname());
            ps.setString(6,user.getAddress());
            ps.setString(7,user.getPhone());
            ps.setInt(8,user.getId());
            return ps.executeUpdate();

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Integer updateStudentPassword(Integer id, String password) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.student set  password=? where id = ?");
            ps.setString(1, MD5.md5Encrypt(password));
            ps.setInt(2, id);
            return ps.executeUpdate();

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) {
       userdaoimp userdao = new userdaoimp();
        Integer i = userdao.EnabledAndDisabled(16, 0);
        System.out.println(i);
    }
}

